package com.kwdb.importtool.core;

import com.kwdb.importtool.config.DatabaseConfig;
import com.kwdb.importtool.config.MigrationConfig;
import com.kwdb.importtool.model.TableSchema;
import com.kwdb.importtool.util.DBConnection;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.concurrent.ConcurrentHashMap;

/**
 * 数据验证器
 * 负责验证迁移前后数据的一致性
 */
@Data
public class DataValidator {
    private static final Logger logger = LoggerFactory.getLogger(DataValidator.class);

    private DatabaseConfig dbConfig;
    private MigrationConfig migrationConfig;
    private Connection kwdbConnection;
    private Connection mysqlConnection;
    private Map<String, TableSchema> tableSchemas;

    // 验证结果
    private Map<String, TableValidationResult> validationResults;
    private boolean overallSuccess = true;
    private List<String> errorMessages;
    private int tablesValidated = 0;
    private int tablesFailed = 0;

    public DataValidator(DatabaseConfig dbConfig, MigrationConfig migrationConfig) {
        this.dbConfig = dbConfig;
        this.migrationConfig = migrationConfig;
        this.tableSchemas = new ConcurrentHashMap<>();
        this.validationResults = new ConcurrentHashMap<>();
        this.errorMessages = Collections.synchronizedList(new ArrayList<>());
    }

    /**
     * 执行完整的数据验证
     */
    public ValidationSummary validateMigration() {
        logger.info("Starting data validation...");
        ValidationSummary summary = new ValidationSummary();

        try {
            // 建立数据库连接
            connectToDatabases();

            // 执行验证步骤
            if (migrationConfig.isValidateSchema()) {
                validateSchemaConsistency();
            }

            if (migrationConfig.isValidateDataCount()) {
                validateDataCountConsistency();
            }

            if (migrationConfig.isValidateDataContent()) {
                validateDataContentConsistency();
            }

            // 生成验证报告
            summary = generateValidationReport();

        } catch (Exception e) {
            logger.error("Data validation failed", e);
            errorMessages.add("Validation process failed: " + e.getMessage());
            overallSuccess = false;
        } finally {
            closeConnections();
        }

        return summary;
    }

    /**
     * 验证模式一致性
     */
    private void validateSchemaConsistency() {
        logger.info("Validating schema consistency...");

        try {
            // 获取MySQL中的表结构
            Map<String, TableSchema> mysqlSchemas = getTableSchemas(mysqlConnection, "MySQL");

            // 获取KWDB中的表结构
            Map<String, TableSchema> kwdbSchemas = getTableSchemas(kwdbConnection, "KWDB");

            // 比较表结构
            compareSchemas(mysqlSchemas, kwdbSchemas);

        } catch (Exception e) {
            logger.error("Schema validation failed", e);
            errorMessages.add("Schema validation failed: " + e.getMessage());
        }
    }

    /**
     * 验证数据数量一致性
     */
    private void validateDataCountConsistency() {
        logger.info("Validating data count consistency...");

        try {
            List<String> tableNames = getTableNames(mysqlConnection);

            for (String tableName : tableNames) {
                if (shouldSkipTable(tableName)) {
                    continue;
                }

                try {
                    long mysqlCount = getRowCount(mysqlConnection, tableName);
                    long kwdbCount = getRowCount(kwdbConnection, tableName);

                    TableValidationResult result = validationResults
                            .computeIfAbsent(tableName, k -> new TableValidationResult(tableName));

                    result.setMySqlRowCount(mysqlCount);
                    result.setKwdbRowCount(kwdbCount);
                    result.setCountMatch(mysqlCount == kwdbCount);

                    if (mysqlCount != kwdbCount) {
                        logger.warn("Row count mismatch for table {}: MySQL={}, KWDB={}",
                                tableName, mysqlCount, kwdbCount);
                        errorMessages.add(String.format(
                                "Row count mismatch for table %s: MySQL=%d, KWDB=%d",
                                tableName, mysqlCount, kwdbCount));
                    } else {
                        logger.info("Row count match for table {}: {}", tableName, mysqlCount);
                    }

                    tablesValidated++;

                } catch (Exception e) {
                    logger.error("Failed to validate row count for table: " + tableName, e);
                    tablesFailed++;
                    errorMessages.add("Failed to validate row count for table " + tableName + ": " + e.getMessage());
                }
            }

        } catch (Exception e) {
            logger.error("Data count validation failed", e);
            errorMessages.add("Data count validation failed: " + e.getMessage());
        }
    }

    /**
     * 验证数据内容一致性
     */
    private void validateDataContentConsistency() {
        if (!migrationConfig.isValidateDataContent()) {
            return;
        }

        logger.info("Validating data content consistency...");

        try {
            List<String> tableNames = getTableNames(mysqlConnection);
            int samplingSize = migrationConfig.getSamplingSize();

            for (String tableName : tableNames) {
                if (shouldSkipTable(tableName)) {
                    continue;
                }

                try {
                    boolean contentMatch = validateTableContent(tableName, samplingSize);

                    TableValidationResult result = validationResults
                            .computeIfAbsent(tableName, k -> new TableValidationResult(tableName));

                    result.setContentMatch(contentMatch);

                    if (!contentMatch) {
                        logger.warn("Content validation failed for table: {}", tableName);
                        errorMessages.add("Content validation failed for table: " + tableName);
                    } else {
                        logger.info("Content validation passed for table: {}", tableName);
                    }

                } catch (Exception e) {
                    logger.error("Failed to validate content for table: " + tableName, e);
                    errorMessages.add("Failed to validate content for table " + tableName + ": " + e.getMessage());
                }
            }

        } catch (Exception e) {
            logger.error("Data content validation failed", e);
            errorMessages.add("Data content validation failed: " + e.getMessage());
        }
    }

    /**
     * 验证单个表的数据内容
     */
    private boolean validateTableContent(String tableName, int samplingSize) {
        try {
            // 获取表结构以确定主键
            TableSchema schema = tableSchemas.get(tableName);
            if (schema == null) {
                logger.warn("No schema information for table: {}, skipping content validation", tableName);
                return false;
            }

            // 获取主键列名（取第一个主键）
            List<String> primaryKeys = schema.getPrimaryKeyColumns();
            String orderBy = "";
            if (primaryKeys != null && !primaryKeys.isEmpty()) {
                orderBy = "ORDER BY " + primaryKeys.get(0);
            }

            String mysqlQuery = String.format(
                    "SELECT * FROM %s %s LIMIT %d", tableName, orderBy, samplingSize);
            String kwdbQuery = String.format(
                    "SELECT * FROM %s %s LIMIT %d", tableName, orderBy, samplingSize);

            List<Map<String, Object>> mysqlData = executeQuery(mysqlConnection, mysqlQuery);
            List<Map<String, Object>> kwdbData = executeQuery(kwdbConnection, kwdbQuery);

            if (mysqlData.size() != kwdbData.size()) {
                return false;
            }

            // 逐行比较数据
            for (int i = 0; i < mysqlData.size(); i++) {
                if (!compareRows(mysqlData.get(i), kwdbData.get(i), schema)) {
                    return false;
                }
            }

            return true;

        } catch (Exception e) {
            logger.error("Content validation failed for table: " + tableName, e);
            return false;
        }
    }

    /**
     * 比较两行数据
     */
    private boolean compareRows(Map<String, Object> mysqlRow, Map<String, Object> kwdbRow, TableSchema schema) {
        Map<String, TableSchema.ColumnDefinition> columns = schema.getColumns();

        for (String column : columns.keySet()) {
            Object mysqlValue = mysqlRow.get(column);
            Object kwdbValue = kwdbRow.get(column);

            if (!Objects.equals(mysqlValue, kwdbValue)) {
                // 获取列数据类型
                TableSchema.ColumnDefinition columnDef = columns.get(column);
                String columnType = columnDef != null ? columnDef.getDataType() : null;

                // 处理特殊类型（如日期时间）的差异
                if (!handleSpecialTypeComparison(mysqlValue, kwdbValue, columnType)) {
                    logger.debug("Value mismatch - Column: {}, MySQL: {}, KWDB: {}",
                            column, mysqlValue, kwdbValue);
                    return false;
                }
            }
        }
        return true;
    }

    /**
     * 处理特殊类型的比较（如日期时间格式差异）
     */
    private boolean handleSpecialTypeComparison(Object mysqlValue, Object kwdbValue, String columnType) {
        if (mysqlValue == null && kwdbValue == null) {
            return true;
        }
        if (mysqlValue == null || kwdbValue == null) {
            return false;
        }

        // 处理时间类型的微小差异
        if (columnType != null && columnType.toLowerCase().contains("time")) {
            String mysqlStr = mysqlValue.toString();
            String kwdbStr = kwdbValue.toString();
            // 允许时间格式的微小差异
            return mysqlStr.replace(" ", "T").equals(kwdbStr.replace(" ", "T"));
        }

        // 处理浮点数的精度差异
        if (columnType != null &&
                (columnType.toLowerCase().contains("float") ||
                        columnType.toLowerCase().contains("double") ||
                        columnType.toLowerCase().contains("decimal"))) {
            try {
                double mysqlDouble = Double.parseDouble(mysqlValue.toString());
                double kwdbDouble = Double.parseDouble(kwdbValue.toString());
                // 允许浮点数有微小的精度差异
                return Math.abs(mysqlDouble - kwdbDouble) < 0.0001;
            } catch (NumberFormatException e) {
                // 如果无法转换为数字，使用字符串比较
                return mysqlValue.toString().equals(kwdbValue.toString());
            }
        }

        return false;
    }

    /**
     * 获取表结构信息
     */
    private Map<String, TableSchema> getTableSchemas(Connection connection, String dbType) throws SQLException {
        Map<String, TableSchema> schemas = new HashMap<>();

        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});

        while (tables.next()) {
            String tableName = tables.getString("TABLE_NAME");
            TableSchema schema = new TableSchema(tableName);

            // 获取列信息
            ResultSet columns = metaData.getColumns(null, null, tableName, null);
            while (columns.next()) {
                String columnName = columns.getString("COLUMN_NAME");
                String columnType = columns.getString("TYPE_NAME");
                int columnSize = columns.getInt("COLUMN_SIZE");
                int decimalDigits = columns.getInt("DECIMAL_DIGITS");
                boolean nullable = "YES".equals(columns.getString("IS_NULLABLE"));

                TableSchema.ColumnDefinition columnDef = new TableSchema.ColumnDefinition();
                columnDef.setColumnName(columnName);
                columnDef.setDataType(columnType);
                columnDef.setSize(columnSize);
                columnDef.setPrecision(decimalDigits);
                columnDef.setNullable(nullable);

                schema.addColumn(columnDef);
            }
            columns.close();

            // 获取主键信息
            ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
            List<String> pkColumns = new ArrayList<>();
            while (primaryKeys.next()) {
                pkColumns.add(primaryKeys.getString("COLUMN_NAME"));
            }
            primaryKeys.close();

            if (!pkColumns.isEmpty()) {
                schema.setPrimaryKey(pkColumns);
            }

            schemas.put(tableName, schema);
            tableSchemas.put(tableName, schema);
        }
        tables.close();

        return schemas;
    }

    /**
     * 比较模式差异
     */
    private void compareSchemas(Map<String, TableSchema> mysqlSchemas, Map<String, TableSchema> kwdbSchemas) {
        Set<String> allTables = new HashSet<>();
        allTables.addAll(mysqlSchemas.keySet());
        allTables.addAll(kwdbSchemas.keySet());

        for (String tableName : allTables) {
            TableSchema mysqlSchema = mysqlSchemas.get(tableName);
            TableSchema kwdbSchema = kwdbSchemas.get(tableName);
            TableValidationResult result = new TableValidationResult(tableName);

            if (mysqlSchema == null) {
                result.setSchemaMatch(false);
                errorMessages.add("Table missing in MySQL: " + tableName);
            } else if (kwdbSchema == null) {
                result.setSchemaMatch(false);
                errorMessages.add("Table missing in KWDB: " + tableName);
            } else {
                boolean match = compareTableSchemas(mysqlSchema, kwdbSchema);
                result.setSchemaMatch(match);
                if (!match) {
                    errorMessages.add("Schema mismatch for table: " + tableName);
                }
            }

            validationResults.put(tableName, result);
        }
    }

    /**
     * 比较两个表结构
     */
    private boolean compareTableSchemas(TableSchema mysqlSchema, TableSchema kwdbSchema) {
        // 比较列数量
        if (mysqlSchema.getColumns().size() != kwdbSchema.getColumns().size()) {
            return false;
        }

        // 比较每个列的定义
        for (String columnName : mysqlSchema.getColumns().keySet()) {
            TableSchema.ColumnDefinition mysqlColumn = mysqlSchema.getColumn(columnName);
            TableSchema.ColumnDefinition kwdbColumn = kwdbSchema.getColumn(columnName);

            if (kwdbColumn == null) {
                return false;
            }

            // 比较数据类型（忽略大小写）
            if (!mysqlColumn.getDataType().equalsIgnoreCase(kwdbColumn.getDataType())) {
                // 检查是否为兼容的数据类型映射
                if (!isCompatibleDataType(mysqlColumn.getDataType(), kwdbColumn.getDataType())) {
                    return false;
                }
            }

            // 比较可空性
            if (mysqlColumn.isNullable() != kwdbColumn.isNullable()) {
                return false;
            }
        }

        return true;
    }

    /**
     * 检查数据类型是否兼容
     */
    private boolean isCompatibleDataType(String mysqlType, String kwdbType) {
        // 常见的兼容数据类型映射
        Map<String, Set<String>> compatibleTypes = new HashMap<>();

        // 整数类型兼容性
        Set<String> integerTypes = new HashSet<>(Arrays.asList(
                "TINYINT", "SMALLINT", "MEDIUMINT", "INT", "INTEGER", "BIGINT"
        ));
        compatibleTypes.put("INTEGER", integerTypes);

        // 浮点类型兼容性
        Set<String> floatTypes = new HashSet<>(Arrays.asList("FLOAT", "REAL", "DOUBLE"));
        compatibleTypes.put("REAL", floatTypes);

        // 字符串类型兼容性
        Set<String> stringTypes = new HashSet<>(Arrays.asList(
                "CHAR", "VARCHAR", "TINYTEXT", "TEXT", "MEDIUMTEXT", "LONGTEXT"
        ));
        compatibleTypes.put("VARCHAR", stringTypes);

        // 检查兼容性
        for (Map.Entry<String, Set<String>> entry : compatibleTypes.entrySet()) {
            if (entry.getValue().contains(mysqlType.toUpperCase()) &&
                    entry.getKey().equalsIgnoreCase(kwdbType)) {
                return true;
            }
        }

        return false;
    }

    /**
     * 获取表名列表
     */
    private List<String> getTableNames(Connection connection) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});

        while (tables.next()) {
            tableNames.add(tables.getString("TABLE_NAME"));
        }
        tables.close();

        return tableNames;
    }

    /**
     * 获取行数
     */
    private long getRowCount(Connection connection, String tableName) throws SQLException {
        String sql = "SELECT COUNT(*) FROM " + tableName;
        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    /**
     * 执行查询并返回结果列表
     */
    private List<Map<String, Object>> executeQuery(Connection connection, String query) throws SQLException {
        List<Map<String, Object>> results = new ArrayList<>();

        try (Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = metaData.getColumnName(i);
                    Object value = rs.getObject(i);
                    row.put(columnName, value);
                }
                results.add(row);
            }
        }

        return results;
    }

    /**
     * 判断是否应该跳过表
     */
    private boolean shouldSkipTable(String tableName) {
        if (tableName == null) {
            return true;
        }

        String lowerTableName = tableName.toLowerCase();
        // 跳过系统表
        return lowerTableName.startsWith("mysql_") ||
                lowerTableName.startsWith("information_schema") ||
                lowerTableName.startsWith("sys_") ||
                lowerTableName.startsWith("pg_");
    }

    /**
     * 建立数据库连接
     */
    private void connectToDatabases() throws SQLException {
        logger.info("Connecting to databases...");

        if (dbConfig.isKwdbConfigValid()) {
            kwdbConnection = DBConnection.getKWDBConnection(dbConfig);
            logger.info("Connected to KWDB: {}", dbConfig.getKwdbUrl());
        }

        if (dbConfig.isMysqlConfigValid() && migrationConfig.shouldValidateMigration()) {
            mysqlConnection = DBConnection.getMySQLConnection(dbConfig);
            logger.info("Connected to MySQL: {}", dbConfig.getMysqlUrl());
        }
    }

    /**
     * 关闭数据库连接
     */
    private void closeConnections() {
        DBConnection.closeConnection(kwdbConnection);
        DBConnection.closeConnection(mysqlConnection);
    }

    /**
     * 生成验证报告
     */
    private ValidationSummary generateValidationReport() {
        ValidationSummary summary = new ValidationSummary();
        summary.setTablesValidated(tablesValidated);
        summary.setTablesFailed(tablesFailed);
        summary.setErrorMessages(new ArrayList<>(errorMessages));
        summary.setOverallSuccess(overallSuccess && tablesFailed == 0);
        summary.setValidationResults(new HashMap<>(validationResults));

        return summary;
    }

    /**
     * 表验证结果
     */
    @Data
    public static class TableValidationResult {
        private String tableName;
        private boolean schemaMatch = true;
        private long mySqlRowCount = 0;
        private long kwdbRowCount = 0;
        private boolean countMatch = true;
        private boolean contentMatch = true;
        private List<String> validationErrors;

        public TableValidationResult(String tableName) {
            this.tableName = tableName;
            this.validationErrors = new ArrayList<>();
        }

        public boolean isOverallSuccess() {
            return schemaMatch && countMatch && contentMatch;
        }
    }

    /**
     * 验证摘要
     */
    @Data
    public static class ValidationSummary {
        private boolean overallSuccess;
        private int tablesValidated;
        private int tablesFailed;
        private List<String> errorMessages;
        private Map<String, TableValidationResult> validationResults;
        private Date validationTime;

        public ValidationSummary() {
            this.validationTime = new Date();
            this.errorMessages = new ArrayList<>();
            this.validationResults = new HashMap<>();
        }

        public int getTablesPassed() {
            return tablesValidated - tablesFailed;
        }

        public double getSuccessRate() {
            return tablesValidated > 0 ? (double) getTablesPassed() / tablesValidated * 100 : 0;
        }
    }
}